import pandas as pd

#保存人员区分信息
staff_area = {
    '宝安' : ['韦泽龙','韦辉辉','黄华义','李德飞','占镐锋','林奇','李永林','苏健金','林文斌','冯宇标','罗峰','罗锋','彭帮忠','丘江','陈卫国','陈雾','雷万成','温晖','张信平','梁远清','刘金鹏','黄瑞钦','陈昌远'],
    '福田' : ['苏辉煌','刘后华','林艺','马宗超','饶亮','谢培强','魏宇佳','罗习海','魏宏','周传光','林丹琪','彭柳荣','龚海林','方敏','罗建','张灿州','莫尊绍','姚峰','林梓涵','邓辉龙','王梓荣','黄浩然','王云','毕伟豪','林志勇','刘祖栎','毛楚铭','张飞','崔二朋','张加雨','陈业威','陈佳椋','黄志浪'],
    '光明' : ['王扬春','吴廷光','陈义','梁宽','鲁进伟','廖伟明','李义群','邓亮','丘满昌','刘达斌','周承豪','邓泉','刘付新杰','黄亚海','张广俊','魏文','甘国辉'],
    '龙岗' : ['黄辉','丘淼','邓华强','李伟伟','刘纯才','朱健伟','朱键伟','张富明','余少群','黎绍金','王学谦','李绰德','陈洪华','林旭波','罗铭贵','邓护峰','邓护锋','谢复荣','陈文彦','陈凤娇','谷兰红','周家龙','张俊华','郭旋','卢志才','刘太松','王毅广','林豪杰','黄文华','朱智琦','王锦城','林秀丽','骆江顺','杨志辉','钟玉霞','郑文伟','马俊泳','林小钻','张福源','邓运财','李云峰','李秀忠','陈灏霖','苏尚居','杨健','梁荣伟'],
    '龙华' : ['支锦文','叶仕锦','熊进','陈嵩锋','阿扎玛提','李文锋','洪灿坚','顾绍飞','邹胜华','陈明','葛长根','袁海堂','张伟传','郑武滔','郑武略','刘社军'],
    '罗湖' : ['黎志腾','彭奕明','范志锐','刘开铭','余建安','曹志辉','杨文景','张壮豪','刘明富','黄锦塘','曾运兵','杨乾'],
    '南山' : ['何小均','张瑶','杨宝峰','吕翀','吕 ','陈学文','曾旭辉','邢凯','洪波','蓝伟方','黄国栋','周世晓','王国杰','陈永健','查捷','陈志环','何文彬','吴金财','周爽','郭江','翁建武','黄远华','林烁','刘俊杰','康旭发','吴鸿炯','邱俊锋','邓嘉壕','欧堂杰','黄文基','郑鸿进'],
    '坪大' : ['黄永茂','刘诗成','钟伟龙','周磊','杨世强','李剑勇','黄康隆','欧伟波','高艳德','官艳德','肖竣'],
    '蛇口前海' : ['罗军华'],
    '盐田' : ['郭志勇','林晓祥','余国均','邓志超','潘嘉明','廖发辉','杨海辉','莫秋辉','刘光伟','肖俊豪']
}

#获取值对应的键函数
def get_key(dic, val):
    for key, value in dic.items():
        if val in value:
            return key
        
#各家积分上限
ZT_UpperLimit = 5400 #中通
CX_UpperLimit = 3000 #长讯
SGC_UpperLimit = 6000 #市工程
ZTJ_UpperLimit = 3000 #中通建/省工程

#各家积分上限字典
dic_UpperLimit = {
    '中通' : ZT_UpperLimit,
    '长讯' : CX_UpperLimit,
    '市工程' : SGC_UpperLimit,
    '中通建' : ZTJ_UpperLimit,
    '省工程' : ZTJ_UpperLimit
}

#代维单位和积分上限的映射
def get_UpperLimit(company):
    return dic_UpperLimit[company]

#在此自定义要处理的表格
df = pd.read_csv('故障处理工单_20240710165348_姚旭茹.csv', dtype='object')

#只保留【单据编号】、【单据标题】、【处理所在班组】、【处理人员】、【处理时间】列
df = df[['单据编号', '单据标题', '处理所在班组', '处理人员', '处理时间']]

#替换【处理所在班组】列的异常值
df.fillna('', inplace=True)

#筛选包含"无线维护团队"字样的行
df = df.query("处理所在班组.str.contains('无线维护团队')")

#去除包含"铁塔"字样的行
df.drop(df[df['处理所在班组'].str.contains('铁塔') == True].index, inplace=True)

#去除非代维人员的工单
df = df[~df['处理所在班组'].isin(['/深圳分公司/综合维护中心/无线维护团队/'])]
df = df[~df['处理所在班组'].isin(['/深圳分公司/无线网络运营中心/无线维护团队/'])]

#【处理时间】列提取 年—月
df['处理时间'] = df['处理时间'].astype(str).apply(lambda x:'-'.join(x.split(' ')[0].split('-')[0:2]))

#插入【区域】列
df.insert(0, '区域', df['处理人员'].apply(lambda x:get_key(staff_area, x)))

#插入【单位】列
df.insert(0, '单位', df['处理所在班组'].apply(lambda x:x.split('/')[-2].split('（')[0]))

#插入【积分上限】列
df.insert(0, '积分上限', df['单位'].apply(lambda x:get_UpperLimit(x)))

#生成一个单位对应工单总数量的透视表
df_WorkNums = df[['单位', '单据编号']].drop_duplicates()
df_WorkNums = df_WorkNums.groupby(['单位']).count()
df_WorkNums = df_WorkNums.reset_index() #取消索引
df_WorkNums = df_WorkNums[['单位', '单据编号']]
df_WorkNums.rename(columns={'单据编号':'工单数量'}, inplace=True)

#将单位工单总数量匹配到df上
df = pd.merge(df, df_WorkNums)

#插入【每单分数】列，并算出每家单位的每张工单的总分数
df.insert(0, '每单分数', df['积分上限']/df['工单数量'])

#插入【单位-单据编号】列													#增加了这一行
df.insert(0, '单位-单据编号', df['单位'] + '-' + df['单据编号'])

#统计每张工单分成了多少行
df_WorkRows = df[['单位-单据编号']]										
df_WorkRows.insert(0, '计数', 1)
df_WorkRows = df_WorkRows.groupby(['单位-单据编号']).count()
df_WorkRows = df_WorkRows.reset_index()
df_WorkRows = df_WorkRows[['单位-单据编号', '计数']]

#将每张工单分成了多少行的数量匹配到df上
df = pd.merge(df, df_WorkRows)

#插入【分值】列，算出每一行的分数
df.insert(0, '分值', df['每单分数']/df['计数'])

df.to_excel('test.xlsx')

#插入【任务类型】列
df.insert(0, '任务类型', '故障处理')

#取每个人重复上的工单数的倒数，算出每个人每张单的单据份额
df['工单数量'] = 1/df['计数']

#重新排序，去除不要的列
df = df[['单据编号', '处理时间', '单位', '区域', '处理人员', '单据标题', '任务类型', '工单数量', '分值']]

#在进行Groupby之前务必把异常值替换成非空值，要不然在进行sum（）时会出现跳过异常值所在行的情况，这里把异常值替换成"None"
df.fillna('None', inplace=True)

#统计
df1 = df.groupby(['处理时间', '单位', '区域', '处理人员', '任务类型']).sum(numeric_only=True)
df2 = df.groupby(['处理人员']).sum(numeric_only=True)

#去除【工单数量】列
df.drop('工单数量', axis=1, inplace=True)

#输出报表
with pd.ExcelWriter('故障处理工单统计.xlsx') as writer:
    df.to_excel(writer, sheet_name='清单', index=False)
    df1.to_excel(writer, sheet_name='统计1')
    df2.to_excel(writer, sheet_name='统计2')
